package com.apes.fn.fico.expense;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface ExpenseItemRepository extends CustomRepository<ExpenseItem, String> {

    /**
     * fetch data by date and tenant id and state
     *
     * @param tenant     tenant id
     * @return List<ExpenseItem>
     */
    @Query("select T from ExpenseItem  T left join T.expense E where T.company.id=?1 and E.state <>'create' and E.state <>'apply' and E.state<>'deducted' and E.state <>'stop' and E.state <>'delete' and ((T.amount - T.amountDeducted -T.withholdingAmount)<>0 or T.amount=0) ")
    List<ExpenseItem> findAllByExpenseCompany(String tenant);

    /**
     * fetch data by date and tenant id and state
     *
     * @param companyId     company id
     * @param startDate     createDate
     * @param endDate     createDate
     * @return List<ExpenseItem>
     */
    @Query("select T from ExpenseItem  T left join T.expense E where T.company.id=?1 and E.state <>'create' and E.state <>'apply'  and E.state <>'stop' and E.state <>'delete'" +
            "  and E.approveDate>=?2 and E.approveDate<?3")
    List<ExpenseItem> findAllByExpenseCompany(String companyId, Date startDate, Date endDate);

    /**
     * fetch last Date by tenant id and ExpenseType
     *
     * @param companyId     company id
     * @param typeId     ExpenseType id
     * @return Date
     */
    @Query("select max(T.periodEnd) from ExpenseItem  T left join T.expense E where T.company.id=?2 and E.state <>'create' and E.state <>'delete' and E.state <>'stop' and T.expenseType.id =?1 and E.creator = 'system' and E.approveId.id = 'system'")
    Date findLastDateByExpenseTypeAndCompany(String typeId, String companyId);

    /**
     * fetch last Date by tenant id and ExpenseType and settlePolicy
     *
     * @param companyId     company id
     * @param typeId     ExpenseType id
     * @param settlePolicy     settlePolicy
     * @return Date
     */
    @Query("select max(T.periodEnd) from ExpenseItem  T left join T.expense E where T.company.id=?2 and E.state <>'create'  and T.expenseType.id =?1  and T.settlePolicy =?3 and T.calculateDate is not null ")
    Date findLastDateBySettlePolicy(String typeId, String companyId,String settlePolicy);

}
